package com;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

public class TestCopyExcel {

	public static final String QC_MAP_TEMPLATE = "D:\\AijunTao\\Desktop\\群创模板\\ReportTemplate.xlsx";
	
	private static final Integer TP_3_6_C_SCI = 0; // C面，序列起始位置
	private static final Integer TP_3_6_T_SCI = 24; // T面，序列起始位置
	private static final Integer TP_3_6_C_SCG = 8; // C面，序列起始位置
	private static final Integer TP_3_6_T_SCG = 32; // T面，序列起始位置
	private static final Integer TP_3_6_STARTDESTINDEX = 16; // 复制模板起始位置

	public static void main(String[] args) throws Exception {

		List<String> glassList = TestCopyExcel.initGlassId();
		
		Map<String, List<DefectGlass>> defectGlassMap = initDefectGlass();
		Map<String, String> defetctMap = initDefectMap();

		File file = new File(QC_MAP_TEMPLATE);
		InputStream input = new FileInputStream(file);
		XSSFWorkbook wb = new XSSFWorkbook(input);
		wb.cloneSheet(0, "NEW_SHEET");
		XSSFSheet sheet = wb.getSheetAt(0);
		int startDestIndex = TP_3_6_STARTDESTINDEX;
		int jumpStep = 14;
		int startIndexRow = 2;
		int maps = (int) Math.ceil(glassList.size() * 1.0 / 2);
		int glassIndex = 1;
		int glassIdIndex = 0;
		XSSFRow row = null;
		String glassId = null;
		// 复制 2-14行，往下复制
		for (int i = 0; i < maps - 1; i++) {
			sheet.copyRows(2, 14, startDestIndex, new CellCopyPolicy());
			startDestIndex += jumpStep;
		}
		
		// 3 17 31 45 59 73 87 101 115 129 143 157
		for (int i = 0; i < maps; i++) {
			row = sheet.getRow(startIndexRow);
			
			// 0 和 24 需要改 index
			row.getCell(TP_3_6_C_SCI).setCellValue(glassIndex++);// 0 和 24 需要改 index
			glassId = getGlassId(glassList, glassIdIndex++);
			row.getCell(TP_3_6_C_SCG).setCellValue(glassId); // 设置GlassID
			
			fillGlassDefect(glassId, sheet, TP_3_6_C_SCI, startIndexRow, defectGlassMap.get(glassId), defetctMap, 3, 16);
			
			row.getCell(TP_3_6_T_SCI).setCellValue(glassIndex++); // 0 和 24 需要改 index
			glassId = getGlassId(glassList, glassIdIndex++);
			row.getCell(TP_3_6_T_SCG).setCellValue(glassId); // 设置GlassID
			
			fillGlassDefect(glassId, sheet, TP_3_6_T_SCI, startIndexRow, defectGlassMap.get(glassId), defetctMap, 3, 16);
			
			startIndexRow += jumpStep;
		}
		
		fillinGlassId(sheet, row, glassList);
		removeNullGlassId(wb, sheet, row, startDestIndex, glassList.size());

		String filePath = "D:\\AijunTao\\Desktop\\群创模板\\" + DateUtil.getSysDate();
		File destPath = new File(filePath);
		if (!destPath.exists()) {
			destPath.mkdir();
		}
		OutputStream stream = new FileOutputStream(filePath + "\\ReportData_" + DateUtil.getSystemTime() + ".xlsx");
		wb.write(stream);
	}

	private static void fillGlassDefect(String glassId, XSSFSheet sheet, int startIndexColumn, int startIndexRow,
			List<DefectGlass> defectGlassList, Map<String, String> defetctMap, int cSideA1, int tSideA1) {
		if (defectGlassList == null)
			return;
		for (DefectGlass d : defectGlassList) {
			String[] poss = d.getPosition().split("\\*");
			int rown = startIndexRow + 6 - Integer.parseInt(poss[0]);
			int colums = startIndexColumn + Integer.parseInt(poss[1]);
			if (d.getSide().equals("C")) {
				if (colums > cSideA1 && startIndexColumn == TP_3_6_C_SCI) {
					colums += 1;
				}
				if (colums > cSideA1 + 24 && startIndexColumn == TP_3_6_T_SCI) {
					colums += 1;
				}
			}
			if (d.getSide().equals("T")) {
				colums = startIndexColumn + 12;
				colums = colums + 7 - Integer.parseInt(poss[1]);
				if (colums < tSideA1 && startIndexColumn == TP_3_6_C_SCI) {
					colums -= 1;
				}
				if (colums < tSideA1 + 24 && startIndexColumn == TP_3_6_T_SCI) {
					colums -= 1;
				}
			}
//			System.out.println(
//					glassId + " SIDE=" + d.getSide() + " POS=" + d.getPosition() + " RealPos=" + rown + " - " + colums);
			XSSFRow row = sheet.getRow(rown);
			if(row == null)
				sheet.createRow(rown);
			if(row.getCell(colums) == null) {
				row.createCell(colums);
			}
//			row.getCell(colums).setCellValue(d.getPosition() + " RL="+ rown + " - " + colums);
			row.getCell(colums).setCellValue(defetctMap.containsKey(d.getCode()) ? defetctMap.get(d.getCode()) : "XXX");
		}

	}

	private static void fillinGlassId(XSSFSheet sheet, XSSFRow row, List<String> glassList) {
		// 设置 AW AX 列的 id 和 GlassID
		for (int id = 1; id <= glassList.size(); id++) {
			row = sheet.getRow(2 + id);
			if(row == null) {
				row = sheet.createRow(2+id);
				row.createCell(48);
				row.createCell(49);
			}
			if(id > 1) {
				row.getCell(48).setCellStyle(sheet.getRow(1+id).getCell(48).getCellStyle());
				row.getCell(49).setCellStyle(sheet.getRow(1+id).getCell(49).getCellStyle());
			}
			row.getCell(48).setCellValue(id);
			row.getCell(49).setCellValue(glassList.get(id - 1));
		}
	}

	/**
	 * 清除空id
	 * @param wb
	 * @param row
	 */
	private static void removeNullGlassId(XSSFWorkbook wb, XSSFSheet sheet, XSSFRow row, Integer startDestIndex, Integer glassSize) {
		XSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setBorderBottom(BorderStyle.NONE); //下边框
		cellStyle.setBorderLeft(BorderStyle.NONE);//左边框
		cellStyle.setBorderRight(BorderStyle.NONE);//右边框
		// 清除不需要的id
		for (int id = glassSize + 1; id <= startDestIndex + 14; id++) {
			row = sheet.getRow(2 + id);
			if(row != null && row.getCell(48) != null && row.getCell(49) != null ) {
				row.getCell(48).setCellValue("");
				row.getCell(49).setCellValue("");
				row.getCell(49).setCellStyle(cellStyle);
			}
		}
	}

	private static String getGlassId(List<String> glassList, int glassIdIndex) {
//		System.out.println("realIndex=" + glassIdIndex + " glassList.size()=" + glassList.size() + " Judge="
//				+ (glassList.size() < glassIdIndex - 2));
		if (glassList.size() - 1 < glassIdIndex) {
			return "";
		}
		return glassList.get(glassIdIndex);
	}

	public static List<String> initGlassId() {
		return Arrays.asList("AZEA2ALC2RC216D", "AZEA2ALC2RD227C", "AZEA2ALC2RC220C", "AZEA2ALC2RC227D",
				"AZEA2ALC2RD223B", "AZEA2ALC2RC204B", "AZEA2ALC2RC232D", "AZEA2ALC2RC220D", "AZEA2AM1006208C",
				"AZEA2ALC2RD223C", "AZEA2ALC2RD210B", "AZEA2AM1006223A", "AZEA2ALC2RD221C", "AZEA2AM1006212C",
				"AZEA2ALC2RD230D", "AZEA2ALC2RC202B", "AZEA2ALC2RD229D", "AZEA2ALC2RD211A", "AZEA2ALC2RD205D",
				"AZEA2AM1006226A", "AZEA2AM1006203C", "AZEA2ALC2RD203A", "AZEA2ALC2RC227C", "AZEA2ALC2RC215C",
				"AZEA2ALC2RC211A", "AZEA2AM1006225A", "AZEA2ALC2RC219C", "AZEA2ALC2RC214A", "AZEA2ALC2RC215A",
				"AZEA2ALC2RC202A", "AZEA2AM1006210B", "AZEA2ALC2RD210A", "AZEA2ALC2RC208A", "AZEA2ALC2RC218D",
				"AZEA2ALC2RD215D", "Ended");
	};

	public static Map<String, String> initDefectMap() {
		List<DefectInfo> defectInfoList = Arrays.asList(new DefectInfo("PGIB0", "來料破片", "B"),
				new DefectInfo("PGIB1", "來料裂痕", "Cr"), new DefectInfo("PGIB2", "來料缺角", "C"),
				new DefectInfo("PGIC3", "來料液晶外漏", "L"), new DefectInfo("PGIC5", "來料面內顏色異常", "I"),
				new DefectInfo("PGIF0", "來料刮傷", "S"), new DefectInfo("PGIF1", "來料髒污", "F"),
				new DefectInfo("PZIF1", "CF 凹點", "D"), new DefectInfo("PZIF2", "CF 凸點", "P"),
				new DefectInfo("PZIF3", "CF 線性凹點", "LD"), new DefectInfo("PZIF4", "CF 刮傷", "S"),
				new DefectInfo("PZIF5", "CF 髒污", "F"), new DefectInfo("PZIF6", "TFT 凹點", "D"),
				new DefectInfo("PZIF7", "TFT 凸點", "P"), new DefectInfo("PZIF8", "TFT 線性凹點", "LD"),
				new DefectInfo("PZIF9", "TFT 刮傷", "S"), new DefectInfo("PZIFA", "TFT 髒污", "F"),
				new DefectInfo("PZZZ1", "來料其他原因", "O"));
		return defectInfoList.stream().collect(Collectors.toMap(DefectInfo::getCode, DefectInfo::getShortCode));
	}

	public static Map<String, List<DefectGlass>> initDefectGlass() {
		List<DefectGlass> defectGlassList = Arrays.asList(
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "3*4"), 
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "3*3"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "3*2"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "3*1"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "3*5"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "3*6"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "2*6"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "2*5"), 
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "2*4"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "2*3"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "2*2"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "2*1"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "1*6"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "1*5"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "1*4"), 
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "1*3"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "1*2"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "T", "1*1"),
				
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "3*4"), 
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "3*3"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "3*2"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "3*1"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "3*5"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "3*6"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "2*6"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "2*5"), 
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "2*4"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "2*3"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "2*2"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "2*1"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "1*6"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "1*5"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "1*4"), 
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "1*3"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "1*2"),
				new DefectGlass("AZEA2ALC2RC220C", "PZIF3", "C", "1*1"),
				
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "3*4"), 
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "3*3"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "3*2"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "3*1"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "3*5"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "3*6"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "2*6"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "2*5"), 
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "2*4"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "2*3"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "2*2"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "2*1"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "1*6"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "1*5"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "1*4"), 
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "1*3"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "1*2"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "T", "1*1"),
				
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "3*4"), 
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "3*3"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "3*2"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "3*1"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "3*5"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "3*6"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "2*6"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "2*5"), 
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "2*4"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "2*3"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "2*2"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "2*1"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "1*6"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "1*5"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "1*4"), 
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "1*3"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "1*2"),
				new DefectGlass("AZEA2ALC2RC204B", "PZIF3", "C", "1*1"),
				
				new DefectGlass("AZEA2ALC2RC218D", "PZIF3", "T", "3*1"),
				new DefectGlass("AZEA2ALC2RC218D", "PZIF3", "T", "3*6"),
				new DefectGlass("AZEA2ALC2RC218D", "PZIF3", "C", "3*3"),
//				
//				new DefectGlass("AZEA2ALC2RC227D", "PZIF3", "C", "1*1"),
//				new DefectGlass("AZEA2ALC2RC227D", "PZIF3", "C", "2*1"),
//				new DefectGlass("AZEA2ALC2RC227D", "PZIF3", "C", "3*1"),
//				new DefectGlass("AZEA2ALC2RC227D", "PZIF3", "C", "1*4"),
//				new DefectGlass("AZEA2ALC2RC227D", "PZIF3", "C", "2*4"),
//				new DefectGlass("AZEA2ALC2RC227D", "PZIF3", "C", "3*4"),
//				
//				new DefectGlass("AZEA2ALC2RC227D", "PZIF3", "T", "1*1"),
//				new DefectGlass("AZEA2ALC2RC227D", "PZIF3", "T", "2*1"),
//				new DefectGlass("AZEA2ALC2RC227D", "PZIF3", "T", "3*1"),
//				new DefectGlass("AZEA2ALC2RC227D", "PZIF3", "T", "1*4"),
				new DefectGlass("AZEA2ALC2RD215D", "PZIF3", "T", "2*4"),
				new DefectGlass("Ended", "PZIF3", "T", "3*4"),
				
				new DefectGlass("AZEA2ALC2RC216D", "PZIF3", "C", "1*1"),
				new DefectGlass("AZEA2ALC2RC216D", "PZIF3", "C", "2*1"),
				new DefectGlass("AZEA2ALC2RC216D", "PZIF3", "C", "3*1"),
				new DefectGlass("AZEA2ALC2RC216D", "PZIF3", "C", "1*4"),
				new DefectGlass("AZEA2ALC2RC216D", "PZIF3", "C", "2*4"),
				new DefectGlass("AZEA2ALC2RC216D", "PZIF3", "C", "3*4"),
				new DefectGlass("Ended", "PZIF3x", "T", "3*1"),
				new DefectGlass("xx", "PZIF3", "T", "1*1")
				);
		return defectGlassList.stream().collect(Collectors.groupingBy(DefectGlass::getGlassId));
	}
}

@Data
@AllArgsConstructor
@NoArgsConstructor
class DefectInfo {
	private String code;
	private String name;
	private String shortCode;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
class DefectGlass {
	private String glassId;
	private String code;
	private String side;
	private String position; // 3*3
}
